The present invention relates generally to the field of information processing by digital computers and, more particularly, to the processing and presentation of information by application programs, particularly electronic spreadsheets.
Before computers, numerical analyses, particularly financial ones, were usually prepared on an accountant's columnar pad or spreadsheet, with pencil and calculator in hand. By organizing data into columns and rows, spreadsheets afford the rapid assimilation of information by a reader. The task of preparing a spreadsheet on paper, however, is not quite so fast. Instead, the process tends to be very slow, as each entry must be tediously calculated and entered into the spreadsheet. Since all calculations are the responsibility of the preparer, manually prepared spreadsheets are also prone to error. Hence, preparation of spreadsheets by hand is slow, tedious, and unreliable.
With the advent of microcomputers, a solution was forthcoming in the form of "electronic spreadsheets." Better known simply as "spreadsheets," these software programs provide a computerized replacement for the traditional financial modeling tools: the accountant's columnar pad, pencil, and calculator. In some regards, spreadsheet programs are to those tools what wordprocessors are to typewriters. Spreadsheets offer dramatic improvements in ease of creating, editing, and using financial models.
A typical spreadsheet program configures the memory of a computer to resemble the column/row or grid format of an accountant's columnar pad, thus providing a visual calculator for a user. Because this "pad" exists dynamically in the computer's memory, however, it differs from paper pads in several important ways. Locations in the electronic spreadsheet, for example, must be communicated to the computer in a format which it can understand. A common scheme for accomplishing this is to assign a number to each row in a spreadsheet, and a letter to each column. To reference a location at column A and row 1 (i.e., the upper-left hand corner), for example, the user types in "A1". In this manner, the spreadsheet defines an addressable storage location or "cell" at each intersection of a row with a column.
Data entry into an electronic spreadsheet occurs in much the same manner that information would be entered on an accountant's pad. After a screen cursor is positioned at a desired location, the user can enter alphanumeric information. Besides holding text and numeric information, however, spreadsheet cells can store special instructions or "formulas" specifying calculations to be performed on the data stored in spreadsheet cells. In this fashion, cell references can serve as variables in an equation, thereby allowing precise mathematical relationships to be defined between cells.
The structure and general operation of a spreadsheet program, including spreadsheet formulas and "macros," are documented in the technical, trade, and patent literature. For an overview, see e.g., Cobb, D., Using 1-2-3, Que Corp., 1985; and Campbell, M., Quattro Pro 4.0 Handbook, 4th Ed., 1992. The disclosures of each of the foregoing references are hereby incorporated by reference.
A particular advantage of electronic spreadsheets is the ability to create a multitude of "what if" scenarios from a single data model. This ability stems largely from the spreadsheet formulas, which are the means by which a user tells an electronic spreadsheet system how to manipulate and analyze one's data. After a set of mathematical relationships has been entered into a worksheet, the spread of information can be "recalculated" using different sets of assumptions, with the results of each recalculation appearing relatively quick. Performing this operation manually, with paper and pencil, would require recalculating every relationship in the model with each change made. Expectedly, electronic spreadsheets have quickly displaced their pencil-and-paper counterparts for modeling user information.
Spreadsheets have always had the capability to handle record and field-oriented data. As a result, one of the most common spreadsheet activities is working with simple databases--simple lists or tables of information. A common method of summarizing data in such a spreadsheet is to generate a cross-tab table. For instance, if a user has a spread consisting of monthly sales data, he or she can generate a two-way cross-tab table showing product names down the left side and regions across the top. The intersection of each row and column displays the total sales for the product for a given region.
Suppose in the prior example that the user wants to extend the analysis to include months. As databases can contain a lot of information, a user will often need to work with data in more than two dimensions. As data becomes more complex, however, conventional spreadsheets are less than an ideal solution. In 1-2-3 for Windows, for instance, a user can generate a summary table from a spread of information (by invoking the Tools.vertline.Database.vertline.Cross-tab command), but the user is limited to two dimensions at a time. For instance, the user can generate a table that shows the total sales amount by region and month, but he or she cannot see sales to new and existing clients in the same table. The same limitation also applies for displaying both the dollar sales and number of sales. Of course the user could perform a query on the table to select new sales and then generate a cross-tab table with the query result, and then perform yet another query to isolate sales to existing customers, and then (finally) generate another cross-tab table. The process would give the user the information desired, but it is, at best, a clumsy approach requiring several steps; moreover, the results would appear in two different tables. Conventional spreadsheets are poorly suited for modeling multi-dimensional information.
Dedicated data modeling programs have long since been known in the art. Based largely on an earlier program named Javelin.TM. (released in 1985), these programs maintain multi-dimensional information as highly structured data models. Improv.TM., from Lotus, is typical of these Data Modeling programs. It is adept at swapping categories between axes so that a user can see a common base of information from different vantage points. Moreover, it enforces the notion of formula generality. Unlike most spreadsheets which store formulas in cells, Improv's formulas are global--meaning that a single formula can produce results in dozens or hundreds of cells. This eliminates the common spreadsheet routine of building a formula and then copying it to adjacent cells. The user enters the global formulas into a special formula pane and the formulas are evaluated in order. Improv's formulas refer to categories and item names rather than cell references. For example, the user is required to express the cost of goods sold as the sum of various named factors (such as Materials.cost+Production.cost), as opposed to references to worksheet coordinates.
Improv's strength is also its weakness. Although it is helpful for modeling multi-dimensional information, it is poorly suited as a general-purpose spreadsheet. Using general formulas has tended to confuse most spreadsheet users. Even the most simple task, such as adding two cells together, becomes a chore with such a product. Accordingly, Improv and its predecessor, Javelin, have both enjoyed little commercial success.
Instead of being forced to adapt radically different approaches to handling spreadsheet information, users want to continue to be able to build spreadsheet models on an ad hoc basis. When multi-dimensional reporting is desired, the users want to be able to take their then-existing spreadsheet file and have the system convert it automatically into the requisite multi-dimensional structure. Accordingly, there is great interest in developing solutions that allow users to continue working in a familiar spreadsheet environment yet, at the same time, allow users to generate multi-dimensional reports on-demand.